# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html
import logging

from .items import CategoryItem,TeacherItem,ChapterItem,CourseItem,CourseDetailItem
import pymysql

# db_config = {
#     "host": "47.103.4.203",
#     "port": 3306,
#     "user": "mystudy_user",
#     "password": "mystudy_pwd",
#     "db": "mystudy",
#     "charset": "utf8",
# }

db_config = {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "root",
    "password": "admin",
    "db": "tz_db",
    "charset": "utf8",
}


logger = logging.getLogger("PipelineLogger")


class CategoryItemPipeline(object):
    def open_spider(self, spider):
        self.conn = pymysql.connect(**db_config)
        self.curs = self.conn.cursor()
        print('mysql 数据库连接成功')

    def close_spider(self, spider):
        self.curs.close()
        self.conn.close()

    def process_item(self, item, spider):
        if isinstance(item, CategoryItem):
            # 1. 核对数据是否已经存放在
            check_exists_sql = "SELECT id FROM tb_course_category WHERE  `id` ='{}' "  #  AND parent_id='{}'
            sql = check_exists_sql.format(item["id"] )                                  # , item["parent_id"]
            self.curs.execute(sql)
            # 若Item进入到该管道中, 而又查询id数据返回的结果包含了数据, 意味着这个Item是重复的.
            # 既然是重复的, 那么就不用执行插入数据的操作了.
            result = self.curs.fetchone()
            # fetchone()返回的结果是一个包含数据的元组或者空元组.
            if result:
                pass
            else:
                # (... create_time, update_time, is_delete)
                sql_column = "id, name, parent_id  , create_time  ,  update_time "
                sql_value = "'{}', '{}', '{}', '{}', {}".format(
                    item["id"], item["name"], item["parent_id"], 'NOW()', 'NOW()')
                sql = "INSERT INTO tb_course_category({}) VALUES ({})".format(sql_column, sql_value)
                try:
                    self.curs.execute(sql)
                    self.conn.commit()
                    print("[+] 分类 插入成功: {}".format(item["name"]))
                except Exception as e:
                    logger.error("[-]分类 insert tb_course_category error!!!: <{}> SQL:<{}>".format(e, sql))
                    self.conn.rollback()

        return item


class TeacherItemPipeline(object):
    def open_spider(self, spider):
        self.conn = pymysql.connect(**db_config)
        self.curs = self.conn.cursor()

    def close_spider(self, spider):
        self.curs.close()
        self.conn.close()

    def process_item(self, item, spider):
        if isinstance(item, TeacherItem):
            # 1. 核对数据是否已经存放在
            check_exists_sql = "SELECT id FROM tb_teachers WHERE `name`='{}' AND `id`='{}' "
            sql = check_exists_sql.format(item["name"], item["id"])
            self.curs.execute(sql)
            # 若Item进入到该管道中, 而又查询id数据返回的结果包含了数据, 意味着这个Item是重复的.
            # 既然是重复的, 那么就不用执行插入数据的操作了.
            result = self.curs.fetchone()
            # fetchone()返回的结果是一个包含数据的元组或者空元组.
            if result:
                pass
            else:
                #   id   name    positional_title    profile   avatar_url   create_time   update_time
                sql_column = " id,  name  ,  positional_title ,  profile ,  avatar_url , create_time  ,  update_time "
                sql_value = " '{}', '{}','{}','{}','{}','{}', '{}' ".format(
                    item["id"], item["name"],item["positional_title"],item["profile"], item["avatar_url"], 'NOW()', 'NOW()' )
                sql = "INSERT INTO tb_teachers({}) VALUES ({})".format(sql_column, sql_value)
                try:
                    self.curs.execute(sql)
                    self.conn.commit()
                    print("[+] 讲师 插入成功: {}".format(item["name"]))
                except Exception as e:
                    logger.error("[-]讲师错误 insert tb_teachers error!!!: <{}> SQL:<{}>".format(e, sql))
                    print('讲师错误:sql',sql)
                    self.conn.rollback()

        return item


class ChapterItemPipeline(object):
    def open_spider(self, spider):
        self.conn = pymysql.connect(**db_config)
        self.curs = self.conn.cursor()
        # 这里spider.conn = self.conn代码的含义: 给爬虫对象创建属性, 创建什么属性?创建Mysql游标和链接属性.
        # 这里的代码让我们能够在爬虫文件中直接通过self.conn使用链接对象和游标对象.
        # 这两个代码写了 能干什么事情,  而这个事情有什么意义?先不管.
        spider.conn = self.conn
        spider.curs = self.curs

    def process_item(self, item, spider):
        if isinstance(item, ChapterItem):
            # # 1. 核对数据是否已经存放在
            # check_exists_sql = "SELECT id FROM tb_chapter WHERE `name`='{}' AND `id`='{}' "
            # sql = check_exists_sql.format(item["name"], item["id"])
            # self.curs.execute(sql)
            # # 若Item进入到该管道中, 而又查询id数据返回的结果包含了数据, 意味着这个Item是重复的.
            # # 既然是重复的, 那么就不用执行插入数据的操作了.
            # result = self.curs.fetchone()
            # # fetchone()返回的结果是一个包含数据的元组或者空元组.
            # if result:
            #     pass
            # else:
            #   id   name   title
            sql_column = " id,  name, title  , create_time  ,  update_time "
            sql_value = " '{}', '{}', '{}', '{}','{}' ".format(  item["id"],  item["name"], item["title"], 'NOW()', 'NOW()' )
            sql = "INSERT INTO tb_chapter({}) VALUES ({})".format(  sql_column, sql_value)
            try:
                self.curs.execute(sql)
                self.conn.commit()
                print("[+] 章节 插入成功: {}".format(item["name"]))
            except Exception as e:
                logger.error("[-]章节错误 insert tb_chapter error!!!:[{}] SQL:[{}]".format(e, sql))
                print('章节错误error:',sql)
                self.conn.rollback()

        return item

    def close_spider(self, spider):
        self.curs.close()
        self.conn.close()



class CourseInfoItemPipeline(object):
    def open_spider(self, spider):
        self.conn = pymysql.connect(**db_config)
        self.curs = self.conn.cursor()
        print('mysql 数据库连接成功')

    def close_spider(self, spider):
        self.curs.close()
        self.conn.close()

    def process_item(self, item, spider):
        if isinstance(item, CourseDetailItem):
            # 1. 核对数据是否已经存放在
            check_exists_sql = "SELECT id FROM tb_course_detail WHERE `title`='{}' AND `id` ='{}' "
            sql = check_exists_sql.format(item["title"], item["id"])
            self.curs.execute(sql)
            # 若Item进入到该管道中, 而又查询id数据返回的结果包含了数据, 意味着这个Item是重复的.
            # 既然是重复的, 那么就不用执行插入数据的操作了.
            result = self.curs.fetchone()
            # fetchone()返回的结果是一个包含数据的元组或者空元组.
            if result:
                pass
            else:
                # id, title,intro, cover_url,content,video_url,price,duration
                # (... create_time, update_time, is_delete)
                sql_column = " id, title,intro, cover_url,content,video_url,price,duration "
                sql_value = "'{}',  '{}', '{}', '{}', '{}', '{}', '{}', '{}' ".format(
                    item["id"], item["title"],  item["intro"],  item["cover_url"],  item["content"],  item["video_url"],  item["price"], item["duration"])
                sql = "INSERT INTO tb_course_detail({}) VALUES ({})".format(sql_column, sql_value)
                try:
                    self.curs.execute(sql)
                    self.conn.commit()
                    print("[+] 详情 插入成功: {}".format(item["title"]))
                except Exception as e:
                    logger.error("[-]详情报错\n insert tb_course_detail error!!!: <{}> SQL:<{}>".format(e, sql))
                    print('详情报错\n',sql)
                    self.conn.rollback()

        return item




class CourseItemPipeline(object):
    def open_spider(self, spider):
        self.conn = pymysql.connect(**db_config)
        self.curs = self.conn.cursor()

    def close_spider(self, spider):
        self.curs.close()
        self.conn.close()

    def process_item(self, item, spider):
        if isinstance(item, CourseItem):
            # 1. 核对数据是否已经存放在
            check_exists_sql = "SELECT id FROM tb_course WHERE `category_id`='{}' AND `title`='{}' "
            sql = check_exists_sql.format( item["category_id"], item["title"])
            self.curs.execute(sql)
            # 若Item进入到该管道中, 而又查询id数据返回的结果包含了数据, 意味着这个Item是重复的.
            # 既然是重复的, 那么就不用执行插入数据的操作了.
            result = self.curs.fetchone()
            # fetchone()返回的结果是一个包含数据的元组或者空元组.
            if result:
                pass
            else:
                """
                #   id  category_id  teacher_id  chapter_id  course_detail_id  title  cover_url  video_url  intro  content  price   duration
                """
                sql_column = " id ,teacher_id , chapter_id , course_detail_id ,category_id ,title , cover_url , video_url , intro , content , price ,  duration"
                sql_value = "'{}', '{}', '{}','{}','{}','{}','{}','{}','{}','{}', '{}',{}".format(
                    item["id"],item["teacher_id"],item["chapter_id"],item["course_detail_id"],
                    item["category_id"], item["title"], item["cover_url"], item["video_url"], item["intro"], item["content"],item["price"], item["duration"])
                sql = "INSERT INTO tb_course({}) VALUES ({})".format(sql_column, sql_value)
                try:
                    self.curs.execute(sql)
                    self.conn.commit()
                    print("[+] 课程 插入成功: {}".format(item["title"]))
                except Exception as e:
                    logger.error("[-]insert tb_course error!!!: <{}> SQL:<{}>".format(e, sql))
                    print('课程的sql错误: {}'.format(sql))
                    self.conn.rollback()

        return item


